﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace EmailClient.DAO
{
    class FilterSpamDAO : ConnectDataBase
    {
        public FilterSpamDAO() : base() { }
        public void AddInfoFilterSpam(string word, int rate, bool spam, string emailAddress)
        {
            ConnectDataBase.ExcuNonQuery("insert into FilterSpam values(" + "'','" + word + "'," + rate + ",'" + spam + "','" + emailAddress + "')");
        }
        public void AddInfoFilterSpam(Dictionary<string, int> dictionary, bool spam, string emailAddress)
        {
            for (int i = 0; i < dictionary.Count; i++)
            {
                AddInfoFilterSpam(dictionary.ElementAt(i).Key, dictionary.ElementAt(i).Value, spam, emailAddress);
            }
        }

        //select word trong từ điển
        public bool IsExistDictionary(string word, bool spam, string emailAddress)
        {
            bool isExist = false;
            DataTable dataTable = new DataTable();
            dataTable = ConnectDataBase.ExcuQuery("select * from FilterSpam where Word ='" + word + "' and Spam='"+ spam +"' and EmailAddress='" + emailAddress + "'");
            if (dataTable.Rows.Count != 0)
                isExist = true;
            return isExist;
        }
      
        public int RateOfWordDictionary(string word, bool spam, string emailAddress)
        {
            int rate = 0;
            DataTable dataTable = new DataTable();
            if (IsExistDictionary(word,spam,emailAddress))
            {
                //query
                dataTable = ConnectDataBase.ExcuQuery("select * from FilterSpam where EmailAddress='" + emailAddress + "' and Spam='"+ spam + "' and Word='" + word + "'");
                rate = dataTable.Rows[0].Field<int>(2);
            }
            return rate;
        }
        public void UpdateDictionary(Dictionary<string, int> dictionary, bool spam, string emailAddress)
        {
            for (int i = 0; i < dictionary.Count; i++)
            {
                if (IsExistDictionary(dictionary.ElementAt(i).Key, spam, emailAddress))
                {
                    //update row exist
                    ConnectDataBase.ExcuNonQuery("update FilterSpam set Rate = " + (RateOfWordDictionary(dictionary.ElementAt(i).Key, spam, emailAddress) + dictionary.ElementAt(i).Value));
                }
                else
                {
                    //insert new row
                    AddInfoFilterSpam(dictionary.ElementAt(i).Key, dictionary.ElementAt(i).Value, spam, emailAddress);
                }
            }
        }

        //load dictionary theo EmailAdress va lop van ban spam hay non spam
        public Dictionary<string, int> LoadDictionary(string emailAddress, bool spam)
        {
            Dictionary<string, int> dictionary = new Dictionary<string, int>();
            DataTable dataTable = new DataTable();
            /*
             * select dictionary cua EmailAddress va thuoc lop spam hay non spam
             */ 
            dataTable = ConnectDataBase.ExcuQuery("select * from FilterSpam where EmailAddress='" + emailAddress + "' and Spam ='" + spam + "'");
            foreach (DataRow row in dataTable.Rows)
            {
                dictionary.Add(row.Field<string>(1), row.Field<int>(2));
            }
            return dictionary;
        }
    }
}
